/**
 * 
 */
package dbAccess;

import java.sql.*;
import java.util.*;

import exceptions.*;

import model.*;

/**
 * @author Anthares & Hennek
 *
 */
public class CommandeEtuDBAccess{
	public ArrayList<Commande_etu> getAllCommandeEtu(int annee, int section_id, ArrayList<String> etats) throws PasIdentificationException, DatabaseErrorException{
		EtudiantDBAccess edba = new EtudiantDBAccess();
		MembreComiteDBAccess mcdba = new MembreComiteDBAccess();
		SyllabusDBAccess sdba = new SyllabusDBAccess();
		LotDBAccess ldba = new LotDBAccess();
		GregorianCalendar gc;
		ArrayList<Commande_etu> tabCom = new ArrayList<Commande_etu>();
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat, stat2;
		
		int nbEtat = 0;
		String addEtat = "";
		if(etats!=null && !(etats.isEmpty())){
			addEtat = " and (";
			for(nbEtat=0;nbEtat<(etats.size()-1);nbEtat++){
				addEtat+="commande_etu.etat = ? or ";
			}
			addEtat+="commande_etu.etat = ?)";
			nbEtat++;
		}
		
		String query;
		String query1 = 		"select * from commande_etu order by date_commande_etu;";
		
		String query2 = 		 "select * from commande_etu, etudiant "
								+"where commande_etu.etudiant_id=etudiant.etudiant_id "
								+"and etudiant.annee = ? order by date_commande_etu;";
		
		String query3 = 		 "select * from commande_etu, etudiant "
								+"where commande_etu.etudiant_id=etudiant.etudiant_id "
								+"and etudiant.section_id = ? order by date_commande_etu;";
		
		String query4 = 		 "select * from commande_etu, etudiant "
								+"where commande_etu.etudiant_id=etudiant.etudiant_id "
								+"and etudiant.annee = ? "
								+"and etudiant.section_id = ?"
								+((etats!=null && !(etats.isEmpty()))?addEtat:"")
								+" order by date_commande_etu;";
		
		try {
			if(annee>0 || section_id>0){
				if(annee==0){
					stat = conn.prepareStatement(query3);
					stat.setInt(1, section_id);
				}
				else{
					if(section_id==0){
						stat = conn.prepareStatement(query2);
						stat.setInt(1, annee);
					}
					else {
						stat = conn.prepareStatement(query4);
						stat.setInt(1, annee);
						stat.setInt(2, section_id);
						if(etats!=null && !(etats.isEmpty())){
							for(int i=0;i<etats.size();i++){
								stat.setString(3+i, etats.get(i).toLowerCase());
							}
						}
					}
				}
			}
			else stat = conn.prepareStatement(query1);
			
			ResultSet data = stat.executeQuery();
			while(data.next()){
				java.sql.Date dat = data.getDate("date_commande_etu");
				gc = new GregorianCalendar();
				gc.setTime(dat);
				Commande_etu com = new Commande_etu(data.getInt("commande_etu_id"), gc, edba.getEtudiant(data.getInt("etudiant_id")), mcdba.getMembreComite(data.getInt("membre_comite_id")), data.getString("etat"), data.getBoolean("paye"));
				
				java.sql.Date dateLiv = data.getDate("date_livraison_etu");
				if(data.wasNull()==false){
					gc = new GregorianCalendar();
					gc.setTime(dateLiv);
					com.setDate_livraison(gc);
				}
				
				String comment = data.getString("commentaires");
				if(data.wasNull()==false) com.setCommentaires(comment);
				
				int numLot = data.getInt("num_lot");
				if(data.wasNull()==false) com.setLot_commande(ldba.getLot(numLot));
				
				query = "select * from ligne_com_etu where commande_etu_id = ?;";
				stat2 = conn.prepareStatement(query);
					stat2.setInt(1, com.getCommande_id());
				ResultSet data2 = stat2.executeQuery();
				while(data2.next()){
					Syllabus s = sdba.getSyllabus(data2.getInt("syllabus_id"));
					com.addSyllabus(s);
				}
				stat2.close();
				tabCom.add(com);
			}
			stat.close();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return tabCom;
	}
	public Commande_etu getCommandeEtu(int commande_id) throws PasIdentificationException, DatabaseErrorException{
		Commande_etu com = null;
		EtudiantDBAccess edba = new EtudiantDBAccess();
		MembreComiteDBAccess mcdba = new MembreComiteDBAccess();
		SyllabusDBAccess sdba = new SyllabusDBAccess();
		GregorianCalendar gc;
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat, stat2;
		String query;
		try {
			query = "select * from commande_etu where commande_etu_id = ?;";
			stat = conn.prepareStatement(query);
				stat.setInt(1, commande_id);
			ResultSet data = stat.executeQuery();
			while(data.next()){
				java.sql.Date dat = data.getDate("date_commande_etu");
				gc = new GregorianCalendar();
				gc.setTime(dat);
				com = new Commande_etu(data.getInt("commande_etu_id"), gc, edba.getEtudiant(data.getInt("etudiant_id")), mcdba.getMembreComite(data.getInt("membre_comite_id")), data.getString("etat"), data.getBoolean("paye"));
				
				java.sql.Date dateLiv = data.getDate("date_livraison_etu");
				if(data.wasNull()==false){
					gc = new GregorianCalendar();
					gc.setTime(dateLiv);
					com.setDate_livraison(gc);
				}
				
				String comment = data.getString("commentaires");
				if(data.wasNull()==false) com.setCommentaires(comment);
				
				int numLot = data.getInt("num_lot");
				if(data.wasNull()==false) com.setLot_commande(new Lot(numLot));
				
				query = "select * from ligne_com_etu where commande_etu_id = ?;";
				stat2 = conn.prepareStatement(query);
				stat2.setInt(1, com.getCommande_id());
				ResultSet data2 = stat2.executeQuery();
				while(data2.next()){
					Syllabus s = sdba.getSyllabus(data2.getInt("syllabus_id"));
					com.addSyllabus(s);
				}
				stat2.close();
			}
			stat.close();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return com;
	}
	public int addCommande(Commande_etu com) throws PasIdentificationException, DatabaseErrorException{
		int com_id = -1;
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query;
		try {
			query = "select max(commande_etu_id) from commande_etu;";
			stat = conn.prepareStatement(query);
			ResultSet data = stat.executeQuery();
			while(data.next()){
				com_id = data.getInt(1);
			}
			stat.close();
			com_id++;
			
			query = "insert into commande_etu (date_commande_etu, etudiant_id, membre_comite_id, paye, etat) values (?,?,?,?,?);";
			stat = conn.prepareStatement(query);
				stat.setDate(1, new java.sql.Date(com.getDate_commande().getTimeInMillis()));
				stat.setInt(2, com.getClient().getId());
				stat.setInt(3, com.getCreateur().getId());
				stat.setBoolean(4, com.isPaye());
				stat.setString(5, com.getEtat());
			stat.executeUpdate();
			stat.close();
			
			if(com.getCommentaires()!=null){
				query = "update commande_etu set commentaires = ? where commande_etu_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setString(1, com.getCommentaires());
					stat.setInt(2, com_id);
				stat.executeUpdate();
				stat.close();
			}
			if(com.getDate_livraison()!=null){
				query = "update commande_etu set date_livraison_etu = ? where commande_etu_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setDate(1, new java.sql.Date(com.getDate_livraison().getTimeInMillis()));
					stat.setInt(2, com_id);
				stat.executeUpdate();
				stat.close();
			}
			if(com.getLot_commande()!=null){
				query = "update commande_etu set num_lot = ? where commande_etu_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setInt(1, com.getLot_commande().getNum_lot());
					stat.setInt(2, com_id);
				stat.executeUpdate();
				stat.close();
			}
			if(com.getContenu()!=null && !(com.getContenu().isEmpty())){
				ArrayList<Syllabus> listSyl = com.getContenu();
				query = "insert into ligne_com_etu (syllabus_id,commande_etu_id) values (?,?);";
				for(Syllabus s : listSyl){
					stat = conn.prepareStatement(query);
						stat.setInt(1, s.getSyllabus_id());
						stat.setInt(2, com_id);
					stat.executeUpdate();
					stat.close();
				}
			}
			conn.commit();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return com_id;
	}
	public void deleteCommandeEtu(Commande_etu com) throws PasIdentificationException, DatabaseErrorException{
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query;
		try {
			query = "delete from ligne_com_etu where commande_etu_id = ?;";
			stat = conn.prepareStatement(query);
				stat.setInt(1, com.getCommande_id());
			stat.executeUpdate();
			stat.close();
			
			query = "delete from commande_etu where commande_etu_id = ?;";
			stat = conn.prepareStatement(query);
				stat.setInt(1, com.getCommande_id());
			stat.executeUpdate();
			stat.close();
			conn.commit();
			
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
	}
	public void modifCommandeEtu(Commande_etu comNew) throws PasIdentificationException, DatabaseErrorException{
		Connection conn = DBConnexionSingleton.getInstance();
		ArrayList<Syllabus> tabOld, tabNew;
		Commande_etu comOld;
		PreparedStatement stat;
		String query;
		try {
			comOld = this.getCommandeEtu(comNew.getCommande_id());
			
			if(comOld.getClient().getId()!=comNew.getClient().getId()){
				query = "update commande_etu set etudiant_id = ? where commande_etu_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setInt(1, comNew.getClient().getId());
					stat.setInt(2, comNew.getCommande_id());
				stat.executeUpdate();
				stat.close();
			}
			if(comOld.getCreateur().getId()!=comNew.getCreateur().getId()){
				query = "update commande_etu set membre_comite_id = ? where commande_etu_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setInt(1, comNew.getCreateur().getId());
					stat.setInt(2, comNew.getCommande_id());
				stat.executeUpdate();
				stat.close();
			}
			if(comNew.getCommentaires()!=null){
				query = "update commande_etu set commentaires = ? where commande_etu_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setString(1, comNew.getCommentaires());
					stat.setInt(2, comNew.getCommande_id());
				stat.executeUpdate();
				stat.close();
			}
			if(comNew.getDate_livraison()!=null && ((comOld.getDate_livraison()==null)?true:!(comOld.getDate_livraison().equals(comNew.getDate_livraison())))){
				query = "update commande_etu set date_livraison_etu = ? where commande_etu_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setDate(1, new java.sql.Date(comNew.getDate_livraison().getTimeInMillis()));
					stat.setInt(2, comNew.getCommande_id());
				stat.executeUpdate();
				stat.close();
			}
			if(!(comOld.getEtat().equals(comNew.getEtat()))){
				query = "update commande_etu set etat = ? where commande_etu_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setString(1, comNew.getEtat());
					stat.setInt(2, comNew.getCommande_id());
				stat.executeUpdate();
				stat.close();
			}
			if(comOld.isPaye()!=comNew.isPaye()){
				query = "update commande_etu set paye = ? where commande_etu_id = ?;";
				stat = conn.prepareStatement(query);
					stat.setBoolean(1, comNew.isPaye());
					stat.setInt(2, comNew.getCommande_id());
				stat.executeUpdate();
				stat.close();
			}
			tabOld = comOld.getContenu();
			tabNew = comNew.getContenu();
			for(Syllabus s : tabOld){
				if(!tabNew.contains(s)){
					query = "delete from ligne_com_etu where syllabus_id = ? and commande_etu_id = ?;";
					stat = conn.prepareStatement(query);
						stat.setInt(1, s.getSyllabus_id());
						stat.setInt(2, comOld.getCommande_id());
					stat.executeUpdate();
					stat.close();
				}
			}
			for(Syllabus s : tabNew){
				if(!tabOld.contains(s)){
					query = "insert into ligne_com_etu (syllabus_id,commande_etu_id) values (?,?);";
					stat = conn.prepareStatement(query);
						stat.setInt(1, s.getSyllabus_id());
						stat.setInt(2, comOld.getCommande_id());
					stat.executeUpdate();
					stat.close();
				}
			}
			conn.commit();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
	}
	public int getNbrComEtu() throws PasIdentificationException, DatabaseErrorException{
		int i = 0;
		Connection conn = DBConnexionSingleton.getInstance();
		PreparedStatement stat;
		String query = "select count(*) from commande_etu;";
		try {
			stat = conn.prepareStatement(query);
			ResultSet data = stat.executeQuery();
			while(data.next()){
				i = data.getInt(1);
			}
			stat.close();
		} catch (SQLException e) {
			throw new DatabaseErrorException(e);
		}
		return i;
	}
}
